using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Globalization;
using System.Xml;
using System.Xml.Xsl;
using APJ.Util;

using System.Data.OleDb;



namespace MTRC.ODMS.Web.SOM.CodeSetup
{
    /// <summary>
    /// author:Weiyudong
    /// </summary>
    public partial class CQAStaffQualificationUpload : BasePage_SOM
    {
        #region MenuID

        protected override string MenuID
        {
            get
            {
                return "M140822";//set the Menu
            }
        }

        #endregion
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                SetLanguage();                
            }
        }

        protected override void OnInit(EventArgs e)
        {
            this.ChangeLanguage += new MTRC.ODMS.Web.BasePage.ChangeLanguageEventHandler(this.ChangeLanguage_Click);
        }

        private void ChangeLanguage_Click(object sender, System.EventArgs e)
        {
            SetLanguage();
        }

        private void SetLanguage()
        {
            this.lblTitle.Text = this.GetLabel("SOM_CQAStaffQualificationUpload");
            this.lblFileToBeUploaded.Text = this.GetLabel("SOM_FileToBeUploaded", true);
            this.btnUpload.Text = this.GetLabel("SOM_Upload");
            this.btnDownTemplte.Text = this.GetLabel("SOM_DownloadEmptyTemplate");
            this.lblUploadNote.Text = this.GetLabel("SOM_Note");
            this.lblDownload.Text = this.GetLabel("SOM_DownloadTheCQAStaffQualification", true);
            this.btnDownMTR.Text = this.GetLabel("SOM_DownloadDown");
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string fileName = "CQAStaffQualificationUpload_ERRORHighlight.xls";
            bool hasERROR = false;
            //check the file if null 
            if (this.txtfileUpload.PostedFile.ContentLength <= 0)
            {
                this.Alert(this.GetMessage("SOM_FilePathNull"));
                return;
            }

            //Add by LiangZehong on 2008-12-18, for issue 0634 Begin...
            string fileType = string.Empty;
            fileType = this.txtfileUpload.PostedFile.FileName.Substring(this.txtfileUpload.PostedFile.FileName.LastIndexOf(".") + 1);
            if (fileType.ToLower() != ("xls").ToLower())
            {
                this.Alert(this.GetMessage("SOM_FileTypeError"));
                return;
            }
            //End...

            XmlDocument xdoc = new XmlDocument();
            try
            {
                string strPath =this.txtfileUpload.PostedFile.FileName;
                
                //File1.PostedFile.SaveAs(strPath);

                //string mystring = "Provider = Microsoft.Jet.OleDb.4.0 ; Data Source = " + strPath + ";Extended Properties=Excel 8.0";

                //OleDbConnection cnnxls = new OleDbConnection(mystring);
                //cnnxls.Open();
                //DataTable dtExcelSchema = cnnxls.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                
                //myDs.ReadXml(strPath);
                //this.txtfileUpload.PostedFile.FileName
                //load file
                xdoc.Load(this.txtfileUpload.PostedFile.InputStream);
                if (xdoc.GetElementsByTagName("Worksheet").Count > 1)
                {
                    Alert(GetMessage("SOM_OneSheetOnly"));
                    return;
                }
                //xdoc.Save(@"C:\hrm.xml");			//for debug
                string xpath = "ss:Workbook/ss:Worksheet/ss:Table/ss:Row[not (@ss:Hidden)]";
                System.Xml.XmlNamespaceManager xnm = new XmlNamespaceManager(xdoc.NameTable);

                xnm.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
                
                XmlNodeList nodelist = xdoc.SelectNodes(xpath, xnm);
                int i = 0;
                XmlDocument xml = new XmlDocument();
                xml.LoadXml("<root/>");
                foreach (XmlNode node in nodelist)
                {
                    if (i > 0)		//0,  first row is head
                    {
                        //string QualCode = "", QualDesc = "", FlagSOMAR = "", FlagObsolete = "";
                        string cqaStaffId = "";
                        string qualificationCode = "";
                        string staffType = "";
                        string staffName = "";
                        string obtainedDate = "";
                        string refreshDate = "";
                        string expiryDate = "";
                        string qualificationStatus = "";
                        try
                        {
                            for (int j = 0, k = 0; j < node.ChildNodes.Count; j++)
                            {
                                if (node.ChildNodes[j].Attributes["ss:Index"] != null)//?
                                {
                                    int h = 0;
                                    if (int.TryParse(node.ChildNodes[j].Attributes["ss:Index"].Value, out h))
                                    {
                                        k = h - 1;
                                    }
                                }
                                switch (k)
                                {
                                    case 0:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            cqaStaffId = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 1:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            qualificationCode = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 2:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            staffType = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 3:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            staffName = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 4:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            obtainedDate = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 5:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            refreshDate = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 6:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            expiryDate = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 7:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            qualificationStatus = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                }
                                k++;
                            }

                            // data validation

                            if ((cqaStaffId == String.Empty) || (cqaStaffId.Length > 40))
                            {
                               // this.Alert(string.Format(this.GetMessage("COM_ParameterLength"), this.GetLabel("SOM_QualificationCode"), "20"));
                                throw new Exception();
                            }

                            if ((qualificationCode == String.Empty) || (qualificationCode.Length > 20) || !MTRC.ODMS.DAL.SOM.CodeSetup.CDCQAQualification.CheckQualCodeExist(qualificationCode))
                            {
                                //this.Alert(string.Format(this.GetMessage("COM_ParameterLength"), this.GetLabel("SOM_QualificationDesc"), "60"));
                                throw new Exception();
                            }

                            if ( staffType != "C" && staffType != "S")
                            {
                                //Alert(this.GetLabel("SOM_FlagSOMAR") + GetMessage("SOM_ValueISYorN"));
                                throw new Exception();
                            }

                            if ((staffName.Trim().Length < 1) || (staffName.Trim().Length>50))
                            {
                                throw new Exception();
                            }


                            if (obtainedDate.Trim().Length<1)
                            {
                                throw new Exception();
                            }

                            if (qualificationStatus.Trim().Length < 1)
                            {
                                throw new Exception();
                            }



                            ////check date time format
                            //DateTime tempDT = new DateTime();
                            //if ( (obtainedDate==string.Empty) )
                            //{
                            //    {
                            //        //obtainedDate = this.ConvertToDateTime(obtainedDate).ToString("dd/MM/yyyy");
                            //        obtainedDate = DateTime.ParseExact(obtainedDate, "dd/MM/yyyy", null).ToString("dd/MM/yyyy");
                            //    }
                            //   // throw new Exception();
                            //}



                            //check date time format
                            if (obtainedDate != string.Empty)
                            {
                                obtainedDate = DateTime.ParseExact(obtainedDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat).ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
                                // throw new Exception();
                            }
                            
                            //can be empty
                            if (refreshDate != string.Empty)
                            {
                                DateTime.ParseExact(refreshDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat);
                            }
                            // can be empty
                            if ( expiryDate != string.Empty)
                            {
                                DateTime.ParseExact(expiryDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat);
                            }
                            if ( qualificationStatus != "Y" && qualificationStatus != "N")
                            {
                                //Alert(this.GetLabel("SOM_VoidYesOrNo") + GetMessage("SOM_ValueISYorN"));
                                throw new Exception();
                            }
                        }
                        catch
                        {
                            i++;
                            hasERROR = true;
                            if (node.ChildNodes[0] != null)
                            {
                                APJ.Util.Utility.AddAttribute(node.ChildNodes[0], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            }
                            if (node.ChildNodes[1] != null)
                            {
                                APJ.Util.Utility.AddAttribute(node.ChildNodes[1], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            }
                            if (node.ChildNodes[2] != null)
                            {
                                APJ.Util.Utility.AddAttribute(node.ChildNodes[2], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            }
                            if (node.ChildNodes[3] != null)
                            {
                                APJ.Util.Utility.AddAttribute(node.ChildNodes[3], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            }
                            if (node.ChildNodes[4] != null)
                            {
                                APJ.Util.Utility.AddAttribute(node.ChildNodes[4], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            }
                            if (node.ChildNodes[5] != null)
                            {
                                APJ.Util.Utility.AddAttribute(node.ChildNodes[5], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            }
                            if (node.ChildNodes[6] != null)
                            {
                                APJ.Util.Utility.AddAttribute(node.ChildNodes[6], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            }
                            if (node.ChildNodes[7] != null)
                            {
                                APJ.Util.Utility.AddAttribute(node.ChildNodes[7], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            }
                           
                            continue;
                        }

                        XmlNode xnode = APJ.Util.Utility.AddNode(xml.FirstChild, "row", "");
                        APJ.Util.Utility.AddAttribute(xnode, "idx", i.ToString());
                        APJ.Util.Utility.AddNode(xnode, "cqaStaffId", "", cqaStaffId);
                        APJ.Util.Utility.AddNode(xnode, "staffType", "", staffType);
                        APJ.Util.Utility.AddNode(xnode, "staffName", "", staffName);
                        APJ.Util.Utility.AddNode(xnode, "qualCode", "", qualificationCode);
                        APJ.Util.Utility.AddNode(xnode, "obtainedDate", "", obtainedDate);
                        APJ.Util.Utility.AddNode(xnode, "refreshDate", "", refreshDate);
                        APJ.Util.Utility.AddNode(xnode, "expiryDate", "", expiryDate);
                        APJ.Util.Utility.AddNode(xnode, "qualStatus", "", qualificationStatus);
                        
                    }
                    i++;
                }
                bool successful = true;
                DataTable dtReturn = new DataTable();
                //if (!hasERROR)
                //{
                    dtReturn = MTRC.ODMS.DAL.SOM.CodeSetup.CDCQAQualification.CQAStaffQualificationUpload(this.ODMUser.ID, this.ODMUser.ID, ref successful, xml);
                //}

                if (successful)
                {


                    if ((dtReturn.Rows.Count > 0) || (hasERROR))
                    {
                        string stylePath = "ss:Workbook/ss:Styles";
                        string stylePathS22 = "ss:Workbook/ss:Styles/ss:Style[@ss:ID='ODMS_ExportExcel']";
                        XmlNode nodeS22 = xdoc.SelectSingleNode(stylePathS22, xnm);
                        if (nodeS22 == null)
                        {//not exist style ODMS, create dynamically for highlight
                            XmlNode nodeStyles = xdoc.SelectSingleNode(stylePath, xnm);
                            XmlNode nodeStyle = APJ.Util.Utility.AddNode(nodeStyles, "Style", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeStyle, "ID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            XmlNode nodeInterior = APJ.Util.Utility.AddNode(nodeStyle, "Interior", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeInterior, "Color", "#FF0000", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            APJ.Util.Utility.AddAttribute(nodeInterior, "Pattern", "Solid", "ss", "urn:schemas-microsoft-com:office:spreadsheet");

                            XmlNode nodeStyle2 = APJ.Util.Utility.AddNode(nodeStyles, "Style", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeStyle2, "ID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            XmlNode nodeInterior2 = APJ.Util.Utility.AddNode(nodeStyle2, "Interior", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeInterior2, "Color", "#FF0000", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            APJ.Util.Utility.AddAttribute(nodeInterior2, "Pattern", "Solid", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            nodeInterior2 = APJ.Util.Utility.AddNode(nodeStyle2, "NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeInterior2, "Format", "General Date", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                        }

                        foreach (DataRow row in dtReturn.Rows)
                        {

                            //---------------------huangyao----------
                            XmlNode node = nodelist.Item((int)row["idx"]);
                            for (int j = 0, k = 0; j < node.ChildNodes.Count; j++)
                            {
                                if (node.ChildNodes[j].Attributes["ss:Index"] != null)
                                {
                                    int h = 0;
                                    if (int.TryParse(node.ChildNodes[j].Attributes["ss:Index"].Value, out h))
                                    {
                                        k = h - 1;
                                    }
                                }

                                APJ.Util.Utility.AddAttribute(node.ChildNodes[j], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");

                                k++;
                            }
                            //--------------------------------------

                            //---------------------commentted by huangyao----------
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[0], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[1], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[2], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[3], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[4], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[5], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[6], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[7], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //---------------end------commentted by huangyao----------
                        }
                        Response.Clear();
                        Response.Charset = "UTF-16";
                        Response.ContentEncoding = System.Text.Encoding.GetEncoding(1200);
                        Response.AddHeader("Content-disposition", "attachment;filename=" + fileName);
                        Response.ContentType = "application/vnd.ms-excel";
                        xdoc.Save(Response.OutputStream);
                        xdoc = null;
                        Response.End();

                    }
                    else
                    {
                        Alert(GetMessage("SOM_UpdateSuccessful"));
                    }
                }
                else
                {
                    xdoc = null;
                    Alert(GetMessage("SOM_UpdateError"));
                }
                xml = null;
            }
            catch (Exception ex)
            {
                Alert(GetMessage("SOM_UpdateError"));
            }
            finally
            {
                xdoc = null;
            }
        }

        protected void btnDownTemplte_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();

            //------------------ Modify by zengshunbo 2008-12-11 actionlog(0709) ---------------------

            DataTable dt = new DataTable(this.GetLabel("SOM_CQAStaffQualificationUpload"));
            dt.Columns.Add(this.GetLabel("SOM_CQAChk"));//CQA ID
            dt.Columns.Add(this.GetLabel("SOM_QualificationCode"));//Qualification Code
            dt.Columns.Add(this.GetLabel("SOM_StaffTypeMore"));//
            dt.Columns.Add(this.GetLabel("SOM_StaffName"));//staff name
            dt.Columns.Add(this.GetLabel("SOM_FirstObtainedDate"));//
            dt.Columns.Add(this.GetLabel("SOM_RefreshDate"));//
            dt.Columns.Add(this.GetLabel("SOM_ExpiryDate"));//
            dt.Columns.Add(this.GetLabel("SOM_QualificationStatus"));//

            //DataTable dt = new DataTable("CQA Staff Qualification Upload");
            //dt.Columns.Add("CQA ID");//CQA ID
            //dt.Columns.Add("Qualification Code");//Qualification Code
            //dt.Columns.Add("Staff Type(C/S) \"C\" for Contractor \"S\" for Staff");//
            //dt.Columns.Add("Staff Name");//staff name
            //dt.Columns.Add("First Obtained Date (DD/MM/YYYY)");//
            //dt.Columns.Add("Refresh Date (DD/MM/YYYY)");//
            //dt.Columns.Add("Expiry Date(DD/MM/YYYY)");//
            //dt.Columns.Add("Qualification Status(Y/N)");//

            //---------------- End Modify by zengshunbo 2008-12-11 actionlog(0709) ---------------------

            ds.Tables.Add(dt);

            Common.ExportExcel(ds, Context, "CQAStaffQualificationUpload.xls");
        }

        protected void btnDownMTR_Click(object sender, EventArgs e)
        {
            DataSet ds = MTRC.ODMS.DAL.SOM.CodeSetup.CDCQAQualification.GetAllCQAQualification();
            DataTable dtSearchResult = ds.Tables[0];
            //dtSearchResult.TableName = this.GetLabel("SOM_CQAStaffQualificationUpload");
            dtSearchResult.TableName = "CQA Staff Qualification Upload";
            dtSearchResult.Columns.Remove("CQAQualificationID");
            dtSearchResult.Columns.Remove("QualDesc");
            dtSearchResult.Columns.Remove("LastUpdUser");
            dtSearchResult.Columns.Remove("LastUpdTime");
            dtSearchResult.Columns.Remove("ActingasUser");
            
            //--------------- Modify by zengshunbo 2008-12-11 actionlog(0709) -------------------

            dtSearchResult.Columns[0].ColumnName = this.GetLabel("SOM_CQAChk");
            dtSearchResult.Columns[1].ColumnName = this.GetLabel("SOM_QualificationCode");
            dtSearchResult.Columns[2].ColumnName = this.GetLabel("SOM_StaffTypeMore");
            dtSearchResult.Columns[3].ColumnName = this.GetLabel("SOM_StaffName");
            dtSearchResult.Columns[4].ColumnName = this.GetLabel("SOM_FirstObtainedDate");
            dtSearchResult.Columns[5].ColumnName = this.GetLabel("SOM_RefreshDate");
            dtSearchResult.Columns[6].ColumnName = this.GetLabel("SOM_ExpiryDate");
            dtSearchResult.Columns[7].ColumnName = this.GetLabel("SOM_QualificationStatus");

            //dtSearchResult.Columns[0].ColumnName = "CQA ID";
            //dtSearchResult.Columns[1].ColumnName = "Qualification Code";
            //dtSearchResult.Columns[2].ColumnName = "Staff Type(C/S) \"C\" for Contractor \"S\" for Staff";
            //dtSearchResult.Columns[3].ColumnName = "Staff Name";
            //dtSearchResult.Columns[4].ColumnName = "First Obtained Date (DD/MM/YYYY)";
            //dtSearchResult.Columns[5].ColumnName = "Refresh Date (DD/MM/YYYY)";
            //dtSearchResult.Columns[6].ColumnName = "Expiry Date(DD/MM/YYYY)";
            //dtSearchResult.Columns[7].ColumnName = "Qualification Status(Y/N)";

            //----------- End Modify by zengshunbo 2008-12-11 actionlog(0709) -------------------

            //dtSearchResult.Columns[0].DataType = System.TypeCode.String;
            //dtSearchResult.Columns[4].DataType = System.TypeCode.DateTime;

            Common.ExportExcel_showHHMM(ds, Context, "CQAStaffQualificationUpload.xls","som_upload");
            dtSearchResult = null;
            ds = null;
        }
    }
}
